CREATE OR REPLACE PACKAGE BODY pkg_email
IS

   --INTERNAL / PRIVATE PROCEDURE 
   PROCEDURE email
      ( recipient IN VARCHAR2
      , cc_recipient IN VARCHAR2 DEFAULT NULL
      , subject IN VARCHAR2
      , bodyText IN VARCHAR2 );

   PROCEDURE email      
      ( recipient IN VARCHAR2
      , cc_recipient IN VARCHAR2 DEFAULT NULL
      , subject IN VARCHAR2
      , bodyText IN VARCHAR2 )
   IS
      mail_conn     utl_smtp.connection;      
   BEGIN
       
   
       mail_conn := utl_smtp.open_connection(g_mailhost, 25);

       utl_smtp.helo(mail_conn, g_mailhost);
       utl_smtp.mail(mail_conn, g_sender);
       utl_smtp.rcpt(mail_conn, recipient);


       utl_smtp.open_data(mail_conn);
       utl_smtp.write_data(mail_conn, 'Subject: '||subject||utl_tcp.CRLF);
       utl_smtp.write_data(mail_conn, 'To: '||recipient||utl_tcp.CRLF);
       utl_smtp.write_data(mail_conn, 'From: '||g_sender|| utl_tcp.CRLF);
       IF cc_recipient IS NOT NULL THEN
          utl_smtp.write_data(mail_conn, 'CC: '||cc_recipient||utl_tcp.CRLF);
       END IF;
   --
   -- Separate the header from the body .
   -- Make sure that there is one blank line, a line containing UTL_TCP.CRLF in it between where you want the 
   -- header to finish and the body to begin.
   -- Otherwise, the body will be embedded in the header - not very useful!
   --
       utl_smtp.write_data(mail_conn, utl_tcp.CRLF);
       utl_smtp.write_data(mail_conn, CHR(10)||bodyText||utl_tcp.CRLF);
       utl_smtp.close_data(mail_conn);
       utl_smtp.quit(mail_conn);   
   
   END;
   
   PROCEDURE p_email_message 
      ( recipient VARCHAR2
      , cc_recipient VARCHAR2 DEFAULT NULL
      , subject VARCHAR2
      , bodyText VARCHAR2 )
   IS
   BEGIN
   
      IF cc_recipient IS NULL THEN
         email
            ( recipient => p_email_message.recipient
            , subject => p_email_message.subject
            , bodyText => p_email_message.bodyText );
      ELSE
         email
            ( p_email_message.recipient
            , p_email_message.cc_recipient
            , p_email_message.subject
            , p_email_message.bodyText );
         email
            ( p_email_message.cc_recipient
            , p_email_message.cc_recipient
            , p_email_message.subject
            , p_email_message.bodyText );               
      END IF;

   EXCEPTION
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE( SUBSTR( SQLERRM, 1, 255 ) );
   END p_email_message;

END pkg_email;
/
